Calculating Working Days Using NETWORKDAYS and WORKDAY in Excel

Course: Advanced Excel

Topic: Calculating Working Days Using NETWORKDAYS and WORKDAY

Course Link: Advanced Excel Course

Demo Link: Register for a Free Course Demo

Website: JustAcademy

1. Introduction to Working Days in Excel

Working days are the days on which an organization normally conducts business. In many workplaces, Saturday and Sunday are treated as weekends, while weekdays are considered working days. Public holidays, company holidays, and other non-working dates may also need to be excluded from calculations.

Microsoft Excel provides two useful date functions for managing working-day calculations:

• NETWORKDAYS: Calculates the number of working days between two dates, excluding weekends and optionally specified holidays.
• WORKDAY: Calculates a date that occurs a specified number of working days before or after a starting date, excluding weekends and optionally specified holidays.

These functions are useful for project scheduling, employee attendance, payroll preparation, delivery planning, service-level agreements (SLAs), deadline management, and business reporting.

2. Learning Objectives

• Understand the purpose of NETWORKDAYS and WORKDAY.
• Learn the syntax and arguments of both functions.
• Calculate working days between two dates.
• Calculate future and past working dates.
• Exclude public holidays from working-day calculations.
• Apply the functions to employee, project, and delivery datasets.
• Understand common date-related errors and troubleshooting techniques.

3. Understanding the NETWORKDAYS Function

The NETWORKDAYS function calculates the number of whole working days between a start date and an end date. By default, it excludes Saturdays and Sundays. You can also supply a list of holidays that should not count as working days.

Syntax

=NETWORKDAYS(start_date, end_date, [holidays])

Function Arguments

Important: NETWORKDAYS counts both the start date and the end date when they fall on working days. Therefore, it is an inclusive calculation rather than simply subtracting one date from another.

Example 1: Calculate Working Days Between Two Dates

Suppose cell A2 contains the start date 05-Jan-2026 and cell B2 contains the end date 16-Jan-2026.

Enter the following formula in C2:

=NETWORKDAYS(A2,B2)

Result: 10 working days.

Explanation: The period contains ten weekdays, excluding the two Saturdays and two Sundays within the date range.

Example 2: Calculate Working Days Excluding Holidays

Suppose the start date is in A2, the end date is in B2, and the holiday dates are listed in cells E2:E4.

=NETWORKDAYS(A2,B2,E2:E4)

Explanation: Excel calculates the weekdays between the two dates and excludes any dates in E2:E4 that fall within the period and would otherwise count as working days.

For example, if the date range contains ten weekdays and two of those weekdays are listed holidays, the result is eight working days.

Example 3: Calculate Working Days for an Employee

Consider the following employee attendance dataset:

Enter the formula in the Working Days column and copy it down for the remaining employees. The formula automatically calculates working days for each employee's date range.

4. Understanding the WORKDAY Function

The WORKDAY function returns a date that falls a specified number of working days before or after a starting date. It excludes Saturdays and Sundays by default and can also exclude a list of holidays.

Syntax

=WORKDAY(start_date, days, [holidays])

Function Arguments

A positive value for days moves forward in time, while a negative value moves backward. A value of zero returns the starting date, subject to Excel's date handling.

Example 1: Calculate a Future Working Date

Suppose cell A2 contains 05-Jan-2026 and you need to determine the date ten working days later.

=WORKDAY(A2,10)

Result: 19-Jan-2026.

Explanation: Excel moves forward ten working days, skipping Saturdays and Sundays. The starting date itself is not counted as the first of the ten days.

Example 2: Calculate a Previous Working Date

Suppose A2 contains 19-Jan-2026 and you need to calculate the date five working days earlier.

=WORKDAY(A2,-5)

Result: 12-Jan-2026.

Explanation: Excel moves backward five working days and skips weekends.

Example 3: Calculate a Delivery Date Excluding Holidays

Suppose a product order begins processing on 05-Jan-2026, and the delivery target is ten working days later. Holiday dates are stored in E2:E4.

=WORKDAY(A2,10,E2:E4)

Explanation: Excel calculates the delivery date while excluding Saturdays, Sundays, and any listed holidays encountered during the calculation.

This formula is particularly useful for logistics teams that estimate dispatch dates, customer delivery commitments, and order-processing deadlines.

5. Difference Between NETWORKDAYS and WORKDAY

6. Using NETWORKDAYS and WORKDAY with Holiday Lists

Businesses often observe public holidays, regional holidays, and company-specific holidays. To improve the accuracy of working-day calculations, maintain a separate holiday list in the worksheet.

Example Holiday Table

Use the holiday dates in formulas, not the holiday names.

Calculate Working Days with Holidays

=NETWORKDAYS(A2,B2,$E$2:$E$4)

The dollar signs create an absolute reference, so the holiday range stays fixed when the formula is copied to other rows.

Calculate a Deadline with Holidays

=WORKDAY(A2,15,$E$2:$E$4)

This returns a date fifteen working days after the starting date, excluding weekends and the specified holidays.

Best practice: Keep holiday cells as valid Excel dates. Text that only looks like a date may cause incorrect calculations or errors.

7. NETWORKDAYS.INTL and WORKDAY.INTL for Custom Weekends

Some organizations do not follow a Saturday-Sunday weekend. For example, certain businesses may operate Sunday through Thursday and treat Friday and Saturday as weekends. Excel provides NETWORKDAYS.INTL and WORKDAY.INTL for such situations.

NETWORKDAYS.INTL Syntax

=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

WORKDAY.INTL Syntax

=WORKDAY.INTL(start_date,days,[weekend],[holidays])

The weekend argument can be a predefined number or a seven-character string representing Monday through Sunday. In the string, 1 means a non-working day and 0 means a working day.

Example 1: Friday and Saturday as Weekends

=NETWORKDAYS.INTL(A2,B2,7)

Explanation: Weekend code 7 treats Friday and Saturday as non-working days.

Example 2: Calculate a Future Date with Friday and Saturday Off

=WORKDAY.INTL(A2,10,7)

This calculates a date ten working days after the starting date, excluding Fridays and Saturdays.

Example 3: Use a Custom Weekend Pattern

=NETWORKDAYS.INTL(A2,B2,"0000110")

The string represents Monday through Sunday. This pattern treats Friday and Saturday as non-working days.

Use these international variants when working with regional calendars, shift-based operations, or organizations with non-standard weekends.

8. Practical Business Applications

Application 1: Project Management

Project managers need to determine the number of working days between a project's start and finish dates.

=NETWORKDAYS(B2,C2,$H$2:$H$10)

This calculates the project duration in working days, excluding weekends and holidays in H2:H10.

Application 2: Employee Leave Tracking

HR teams can calculate working days between an employee's leave start and end dates. This helps estimate leave duration when weekends and specified holidays should not count.

=NETWORKDAYS(B2,C2,$H$2:$H$10)

Apply the formula according to the organization's leave policy. Some policies treat public holidays during leave differently, so confirm the rules before using the result for payroll or leave balances.

Application 3: Customer Delivery Deadlines

Operations teams can estimate when an order will be ready by adding the required processing days to the order date.

=WORKDAY(B2,C2,$H$2:$H$10)

Here, B2 contains the order date, C2 contains the required processing days, and H2:H10 contains holidays.

Application 4: SLA Deadline Calculation

Customer support teams may need to respond within a specified number of business days.

=WORKDAY(A2,3,$H$2:$H$10)

This calculates a date three working days after the ticket date, excluding weekends and listed holidays. If the SLA uses hours, different time-based calculations may be necessary.

Application 5: Invoice Due-Date Planning

Finance teams can calculate a due date a set number of working days after an invoice date.

=WORKDAY(A2,7,$H$2:$H$10)

This returns a date seven working days after the invoice date. Always check whether the contract defines the due date in calendar days or working days.

9. Common Errors and Troubleshooting

10. Best Practices for Accurate Working-Day Calculations

• Store dates as valid Excel date values rather than plain text.
• Maintain one centralized holiday list for consistent reporting.
• Use absolute references for holiday ranges when copying formulas.
• Choose NETWORKDAYS when the required output is a count.
• Choose WORKDAY when the required output is a future or previous date.
• Use the international versions when the weekend pattern differs from Saturday and Sunday.
• Test calculations around weekends, holidays, month-end dates, and year-end dates.
• Confirm the organization's rules for leave, delivery, and SLA calculations.
• Format WORKDAY results as dates so they are easy to read.
• Document whether the calculation counts the start date, especially when applying business-specific deadline policies.

11. Practice Exercises

Exercise 1: Calculate Working Days

Cell A2 contains 02-Feb-2026 and B2 contains 13-Feb-2026.

• Use NETWORKDAYS to calculate the working days between the two dates.
• Explain how Saturdays and Sundays affect the result.

Exercise 2: Exclude Holidays

Use the same date range and place a holiday date that falls within the period in E2.

• Calculate working days without holidays.
• Calculate working days using E2 as the holiday argument.
• Compare the two results.

Exercise 3: Find a Future Deadline

Cell A2 contains 02-Feb-2026.

• Use WORKDAY to find the date five working days later.
• Repeat the calculation with a negative value of five.
• Compare the resulting dates.

Exercise 4: Create a Delivery Schedule

Create a table containing order IDs, order dates, processing days, holiday dates, and estimated completion dates.

• Use WORKDAY to calculate completion dates.
• Use NETWORKDAYS to measure the working-day duration between order and completion dates.
• Verify the results against a calendar.

Exercise 5: Configure a Custom Weekend

Assume Friday and Saturday are non-working days.

• Use NETWORKDAYS.INTL to calculate working days.
• Use WORKDAY.INTL to find a date ten working days after a start date.
• Explain why the standard NETWORKDAYS and WORKDAY functions may not be appropriate for this schedule.

12. Mini Project: Project Deadline and Working-Day Tracker

Objective: Build an Excel worksheet that calculates project durations and expected completion dates.

Suggested Dataset

Project Steps

1. Create a project table with start dates and estimated durations.
2. Prepare a separate list of company holidays.
3. Use WORKDAY to calculate expected completion dates.
4. Use NETWORKDAYS to calculate the working days between start and completion dates.
5. Format date columns consistently.
6. Test the formulas using dates that cross weekends and holidays.
7. Apply conditional formatting to highlight approaching deadlines.
8. Review the results and document the scheduling assumptions.

Expected Outcome: A reusable project tracker that helps teams estimate completion dates and measure project durations while accounting for weekends and holidays.

13. Interview Questions and Answers

Q1. What is the NETWORKDAYS function?

Answer: NETWORKDAYS calculates the number of working days between two dates, excluding Saturday and Sunday by default and optionally excluding specified holidays.

Q2. What is the WORKDAY function?

Answer: WORKDAY returns a date a specified number of working days before or after a starting date, excluding weekends and optional holidays.

Q3. What is the main difference between NETWORKDAYS and WORKDAY?

Answer: NETWORKDAYS returns a count of working days, whereas WORKDAY returns a calculated date.

Q4. How can holidays be excluded from a calculation?

Answer: Supply a range of valid holiday dates as the optional holidays argument.

Q5. Can WORKDAY calculate a date in the past?

Answer: Yes. Use a negative value for the days argument.

Q6. How do you calculate working days when Friday and Saturday are weekends?

Answer: Use NETWORKDAYS.INTL with weekend code 7, or use the corresponding custom weekend pattern.

Q7. Does NETWORKDAYS count the start and end dates?

Answer: It includes both boundary dates when they qualify as working days and are not listed holidays.

Q8. Why might WORKDAY display a number instead of a date?

Answer: Excel stores dates as serial numbers. Format the result cell as a Date to display it in a readable date format.

Q9. Can NETWORKDAYS and WORKDAY be used for payroll?

Answer: Yes, they can support payroll and attendance calculations, provided the formulas reflect the organization's actual leave, weekend, and holiday policies.

Q10. What happens if the starting date falls on a weekend?

Answer: NETWORKDAYS excludes it from the working-day count, while WORKDAY moves the specified number of working days from the starting date according to its weekend and holiday rules.

14. Quick Revision Checklist

• NETWORKDAYS counts working days between two dates.
• WORKDAY calculates a date a specified number of working days away.
• Both functions exclude Saturday and Sunday by default.
• Both functions support optional holiday ranges.
• Negative WORKDAY values calculate earlier dates.
• NETWORKDAYS.INTL and WORKDAY.INTL support custom weekend schedules.
• Holiday entries should be valid Excel dates.
• Use absolute references for holiday lists when copying formulas.
• Format calculated dates as Date values.
• Validate formulas against the business calendar and applicable policies.

15. Conclusion

NETWORKDAYS and WORKDAY are essential Excel functions for managing business schedules, employee attendance, delivery dates, project timelines, and service deadlines. NETWORKDAYS helps determine the number of working days between two dates, while WORKDAY calculates a future or previous working date.

By incorporating holiday lists, applying custom weekend functions where necessary, and checking date formats, you can build reliable scheduling worksheets and reduce manual errors. These skills are valuable for advanced Excel users, data analysts, project coordinators, HR professionals, and finance teams.

Learn more through the JustAcademy Advanced Excel Course or register for a course demo.